import time
from operator import itemgetter 
from itertools import groupby 
from urllib.parse import urlparse,parse_qsl 

import requests as rq
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://server_log:qriUTu5iNt3IKuwMsxGW@192.168.229.18:5029/server_log", max_overflow=5)


#从指定地址返回json格式的数据
def rq_data(url,payload):
    r = rq.get(url,params=payload)
    return r.json()

#格式化数据
def format_list_to_dataframe(lst):
    dic = {}
    for d in lst:
        for k in d:
            if k not in dic: dic[k] = []
            dic[k].append(d[k])
    return pd.DataFrame(dic)

#导出csv
def export_csv(df,name):
    df.to_csv('csv/'+name+".csv",index=False,sep=',')

#去重
def uniq(lst):
    data = []
    for i in lst:
        if i not in data:
            data.append(i)
    return data

#去重列表
def uniq_list(lst,key):
    key_list = []
    res = []
    for dic in lst:
        key_val = dic[key]
        if key_val not in key_list:
            key_list.append(key_val)
            res.append(dic)
    return res

def pd_col_list(df,col):
    lst = df[col].values.tolist()
    return lst

#连接字符串
def str_list_to_str(lst):
    return '"'+'","'.join(lst)+'"'

def num_list_to_str(lst):
    return ','.join([str(n) for n in lst])

#查询数据 
def select_data(sql):
    result = engine.execute(sql)
    rows = result.fetchall()
    items = []
    for row in rows:
        items.append(dict(zip(row.keys(),row.values())))
    return items

def valid_custom_id_filter_fun(custom_id):
    return int(custom_id)>0

#时间处理
def datetime_to_timestamp(dt):
    #时间 -> 时间戳
    return int(time.mktime(time.strptime(dt, "%Y-%m-%d %H:%M:%S")))

def day_to_timestamp(day):
    #日期 -> 时间戳
    return int(time.mktime(time.strptime(day, "%Y-%m-%d")))

def timestamp_to_day(ts):
    #时间戳 -> 日期
    return time.strftime("%Y-%m-%d", time.localtime(ts))

def timestamp_to_datetime(ts):
    #时间戳 -> 时间
    return time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(ts))

def timestamp_to_month(ts):
    #时间戳-> 月份
    return time.strftime("%Y%m", time.localtime(ts))

def day_to_month(day):
    #日期 -> 月份
    return timestamp_to_month(day_to_timestamp(day))

def get_ly_list(day):
    #获取无效留言
    ly_list = rq_data('http://u.2958.cn/statistic/invalid.php',{'day':day,'type':1,'filter':1})
    return format_list_to_dataframe(ly_list)

def get_his_list(day,uids):
    #获取历史访问
    month = day_to_month(day)
    his_sql = "SELECT ip,uid,page_url AS fromurl,refer_page_url AS reffer_url,gid AS custom_id,display_res AS screen,CONCAT_WS(' ',os_name,os_major) AS os,CONCAT_WS(' ',browser_name,browser_major) AS browser,device_vender AS device,datetime AS time,extra07 AS iswx FROM DB_ALL_PV_%s WHERE day='%s' AND uid IN (%s)" % (month,day,str_list_to_str(uids))
    his_list = select_data(his_sql)
    return format_list_to_dataframe(his_list)

def get_custom_list(custom_ids):
    #获取客户信息
    custom_list = rq_data('http://gballs.2958.cn/api/Seo/custom.php',{'key':'POHdrVr7S4Mr7o9','function':'getCustoms','custom_id':num_list_to_str(custom_ids)})
    return format_list_to_dataframe(uniq_list(custom_list['data'],'custom_id'))

def get_ip_addr_list(ips):
    #获取ip地理信息
    ip_sql = "SELECT ip,province,city FROM geolocation_log WHERE ip IN(%s) GROUP BY ip" % str_list_to_str(ips)
    ip_address_list = select_data(ip_sql)
    return format_list_to_dataframe(ip_address_list)

def export_data(day):
    #导出合并后的数据
    print('\n')
    print(day)

    print('获取无效留言')
    ly_list_df = get_ly_list(day)
    ly_list_df[['custom_id']] = ly_list_df[['custom_id']].apply(pd.to_numeric)

    print('获取历史访问')
    uids =  uniq(pd_col_list(ly_list_df,'uid'))
    his_list_df = get_his_list(day,uids)

    print('获取客户信息')
    custom_ids = list(filter(valid_custom_id_filter_fun,uniq(pd_col_list(ly_list_df,'custom_id')+pd_col_list(his_list_df,'custom_id'))))
    custom_list_df = get_custom_list(custom_ids)
    custom_list_df[['custom_id']] = custom_list_df[['custom_id']].apply(pd.to_numeric)

    print('获取地域信息')
    ips = uniq(pd_col_list(ly_list_df,'ip')+pd_col_list(his_list_df,'ip'))
    ip_addr_list_df = get_ip_addr_list(ips)

    print('生成留言数据')
    ly_data_df = pd.merge(pd.merge(ly_list_df,custom_list_df),ip_addr_list_df)
    export_csv(ly_data_df,day+'_ly_data')

    print('生成历史访问数据')
    his_data_df = pd.merge(pd.merge(his_list_df,custom_list_df),ip_addr_list_df)
    export_csv(his_data_df,day+'_his_data')

def get_day_list(sd,ed):
    st = day_to_timestamp(sd)
    et = day_to_timestamp(ed)
    days = []
    while st<=et:
        days.append(timestamp_to_day(st))
        st += 24*60*60
    return days

def export_data_by_days(days):
    print('start')
    for day in days:
        export_data(day)
    print('end')

def export_his_data(days):
    print('start')
    his_dfs = []
    for day in days:
        his_dfs.append(pd.read_csv('csv/'+day+'_his_data.csv'))
    his_df = pd.concat(his_dfs)
    export_csv(his_df,'his_data')
    print('end')

def export_ly_data(days):
    print('start')
    ly_dfs = []
    for day in days:
        ly_dfs.append(pd.read_csv('csv/'+day+'_ly_data.csv'))
    ly_df = pd.concat(ly_dfs)
    export_csv(ly_df,'ly_data')
    print('end')

# days = get_day_list('2018-07-01','2018-07-31')






